Materialize Subplan and push into inner index conditions - Mailing list pgsql-performance

Is it possible to have the planner consider the second plan instead of the
first?

admpostgres4=> explain analyze select * from users where id in (select
user_id from user2user_group where user_group_id = 769694);

QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash IN Join  (cost=4.04..2302.05 rows=4 width=78) (actual
time=50.381..200.985 rows=2 loops=1)
   Hash Cond: ("outer".id = "inner".user_id)
   ->  Append  (cost=0.00..1931.68 rows=77568 width=78) (actual
time=0.004..154.629 rows=76413 loops=1)
         ->  Seq Scan on users  (cost=0.00..1024.88 rows=44588 width=78)
(actual time=0.004..36.220 rows=43433 loops=1)
         ->  Seq Scan on person_user users  (cost=0.00..906.80 rows=32980
width=78) (actual time=0.005..38.120 rows=32980 loops=1)
   ->  Hash  (cost=4.04..4.04 rows=2 width=4) (actual time=0.020..0.020
rows=2 loops=1)
         ->  Index Scan using user2user_group_user_group_id_idx on
user2user_group  (cost=0.00..4.04 rows=2 width=4) (actual time=0.011..0.014
rows=2 loops=1)
               Index Cond: (user_group_id = 769694)
 Total runtime: 201.070 ms
(9 rows)

admpostgres4=> select user_id from user2user_group where user_group_id =
769694;
 user_id
---------
  766541
  766552
(2 rows)

admpostgres4=> explain analyze select * from users where id in (766541,
766552);
                                                                 QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=4.02..33.48 rows=9 width=78) (actual time=0.055..0.087
rows=2 loops=1)
   ->  Append  (cost=4.02..33.48 rows=9 width=78) (actual time=0.051..0.082
rows=2 loops=1)
         ->  Bitmap Heap Scan on users  (cost=4.02..18.10 rows=5 width=78)
(actual time=0.051..0.053 rows=2 loops=1)
               Recheck Cond: ((id = 766541) OR (id = 766552))
               ->  BitmapOr  (cost=4.02..4.02 rows=5 width=0) (actual
time=0.045..0.045 rows=0 loops=1)
                     ->  Bitmap Index Scan on users_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.034..0.034 rows=1 loops=1)
                           Index Cond: (id = 766541)
                     ->  Bitmap Index Scan on users_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.008..0.008 rows=1 loops=1)
                           Index Cond: (id = 766552)
         ->  Bitmap Heap Scan on person_user users  (cost=4.02..15.37
rows=4 width=78) (actual time=0.025..0.025 rows=0 loops=1)
               Recheck Cond: ((id = 766541) OR (id = 766552))
               ->  BitmapOr  (cost=4.02..4.02 rows=4 width=0) (actual
time=0.023..0.023 rows=0 loops=1)
                     ->  Bitmap Index Scan on person_user_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.017..0.017 rows=0 loops=1)
                           Index Cond: (id = 766541)
                     ->  Bitmap Index Scan on person_user_id_idx
(cost=0.00..2.01 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                           Index Cond: (id = 766552)
 Total runtime: 0.177 ms
(17 rows)

admpostgres4=>

admpostgres4=> \d users;
                          Table "adm.users"
      Column      |            Type             |      Modifiers
------------------+-----------------------------+---------------------
 id               | integer                     | not null
 classid          | integer                     | not null
 revision         | integer                     | not null
 rev_start        | timestamp without time zone |
 rev_end          | timestamp without time zone |
 rev_timestamp    | timestamp without time zone | not null
 rev_state        | integer                     | not null default 10
 name             | character varying           |
 password         | character varying           |
 password_expires | timestamp without time zone |
 password_period  | integer                     |
Indexes:
    "users_pkey" primary key, btree (revision)
    "users_uidx" unique, btree (revision)
    "users_id_idx" btree (id)
    "users_name_idx" btree (rev_state, rev_end, name)
    "users_rev_end_idx" btree (rev_end)
    "users_rev_idx" btree (rev_state, rev_end)
    "users_rev_start_idx" btree (rev_start)
    "users_rev_state_idx" btree (rev_state)
Inherits: revision

admpostgres4=>\d person_user;
                       Table "adm.person_user"
      Column      |            Type             |      Modifiers
------------------+-----------------------------+---------------------
 id               | integer                     | not null
 classid          | integer                     | not null
 revision         | integer                     | not null
 rev_start        | timestamp without time zone |
 rev_end          | timestamp without time zone |
 rev_timestamp    | timestamp without time zone | not null
 rev_state        | integer                     | not null default 10
 name             | character varying           |
 password         | character varying           |
 password_expires | timestamp without time zone |
 password_period  | integer                     |
 lastname         | character varying           |
 description      | character varying           |
 vat_id           | character varying           |
 firstname        | character varying           |
 sex              | integer                     |
 birthdate        | timestamp without time zone |
 title            | character varying           |
Indexes:
    "person_user_pkey" primary key, btree (revision)
    "person_user_uidx" unique, btree (revision)
    "person_user_id_idx" btree (id)
    "person_user_rev_end_idx" btree (rev_end)
    "person_user_rev_idx" btree (rev_state, rev_end)
    "person_user_rev_start_idx" btree (rev_start)
    "person_user_rev_state_idx" btree (rev_state)
Inherits: users

admpostgres4=>

admpostgres4=> \d user2user_group;
     Table "adm.user2user_group"
    Column     |  Type   | Modifiers
---------------+---------+-----------
 user_id       | integer | not null
 user_group_id | integer | not null
Indexes:
    "user2user_group_pkey" primary key, btree (user_id, user_group_id)
    "user2user_group_uidx" unique, btree (user_id, user_group_id)
    "user2user_group_user_group_id_idx" btree (user_group_id)
    "user2user_group_user_id_idx" btree (user_id)

admpostgres4=>

Mit freundlichem Gruß
Jens Schicke
--
Jens Schicke              j.schicke@asco.de
asco GmbH              http://www.asco.de
Mittelweg 7              Tel 0531/3906-127
38106 Braunschweig          Fax 0531/3906-400